﻿/*
 * Created by SharpDevelop.
 * User: André Casaca
 * Date: 02-07-2011
 * Time: 17:43
 * 
 * To change this template use Tools | Options | Coding | Edit Standard Headers.
 */
using System;
using System.Collections.Generic;
using System.Data;
using Mono.Data.SqliteClient;
using System.Text;

namespace LP_C__5847_2010_2011
{
	/// <summary>
	/// Do all the Statistics needed.
	/// </summary>
	public class Statistics
	{
		/// <summary>
        /// The constructor does nating
        /// </summary>
		public Statistics()
		{
			Console.WriteLine("I only do Statistics!\n");
		}
		
		/// <summary>
        /// Get Number of Teachers
        /// </summary>
        /// <param name="fromYear">Year of the data</param>
        /// <returns>The amount of Teachers</returns>
		public static int GetNumTeachers(int fromYear){
			IDbConnection dbcon = (IDbConnection) new SqliteConnection("URI=file:rebides.db,version=3");
			dbcon.Open();
			IDbCommand dbcmd = dbcon.CreateCommand();

			dbcmd.CommandText = "SELECT ano FROM fichas_docencia where ano = " + fromYear;
			
			int numTeachers = 0;
			IDataReader reader = dbcmd.ExecuteReader();
			while(reader.Read()) {
				numTeachers++;
			}
			// clean up
			reader.Close();
			reader = null;
			dbcmd.Dispose();
			dbcmd = null;
			dbcon.Close();
			dbcon = null;
			
			return numTeachers;
		}
		
        /// <summary>
        /// Get The List of Any data on rebides.db
        /// </summary>
        /// <param name="what">Colum to analyze</param>
        /// <param name="FromWere">Table to intenerate</param>
        /// <returns>A Dictionary ready to count any data</returns>
		public static Dictionary<string, int> GetList(string what, string fromWhere){
			Dictionary<string, int> dataDictionary = new Dictionary<string, int>();

			IDbConnection dbcon = (IDbConnection) new SqliteConnection("URI=file:rebides.db,version=3");
			dbcon.Open();
			
			IDbCommand dbcmd;
			IDataReader reader;
			if(!fromWhere.Contains(",")){
				dbcmd = dbcon.CreateCommand();
				dbcmd.CommandText = "SELECT " + what + " FROM " + fromWhere;
				reader = dbcmd.ExecuteReader();
				while(reader.Read()) {
					string designation = reader.GetString (0);
					dataDictionary.Add(designation, 0);
				}
			}else{
				dbcmd = dbcon.CreateCommand();
				
				string[] splitStr = fromWhere.Split(new char[]{','});
				
				List<string> list1stStr = new List<string>();
				dbcmd.CommandText = "SELECT " + what + " FROM " + splitStr[0];
				reader = dbcmd.ExecuteReader();
				while(reader.Read()) {
					string designation = reader.GetString (0);
					list1stStr.Add(designation);
				}
				
				CleanUp(reader, dbcmd);
				
				dbcmd = dbcon.CreateCommand();
				List<string> list2ndStr = new List<string>();
				dbcmd.CommandText = "SELECT " + what + " FROM " + splitStr[1];
				reader = dbcmd.ExecuteReader();
				while(reader.Read()) {
					string designation = reader.GetString (0);
					list2ndStr.Add(designation);
				}
				
				foreach (string a in list1stStr)
				{
					foreach(string b in list2ndStr)
					{
						dataDictionary.Add(a + " - " + b, 0);
					}
				}
			}
			// clean up
			CleanUp(reader, dbcmd);
			dbcon.Close();
			dbcon = null;
			return dataDictionary;
		}
		
        /// <summary>
        /// Clear Up the buffers Of Database Reader
        /// </summary>
        /// <param name="reader">Reader of DataBase</param>
        /// <param name="bdcmd">Comand alredy Readed to clean</param>
		public static void CleanUp(IDataReader reader, IDbCommand dbcmd){
			reader.Close();
			reader = null;
			dbcmd.Dispose();
			dbcmd = null;
		}
        
		/// <summary>
        /// Get The List of Teachers on year
        /// </summary>
        /// <param name="fromYear">Year of the data</param>
        /// <returns>The List of Teachers</returns>
		public static List<Person> GetListTeachers(int fromYear){
			List<Person> listTeacher = new List<Person>();
			IDbConnection dbcon = (IDbConnection) new SqliteConnection("URI=file:rebides.db,version=3");
			dbcon.Open();
			IDbCommand dbcmd = dbcon.CreateCommand();

			dbcmd.CommandText = "SELECT ano, docente, estabelecimento, categoria, regime, grau, tipo_estabelecimento FROM fichas_docencia where ano = " + fromYear;
			
			IDataReader reader = dbcmd.ExecuteReader();
			while(reader.Read()) {
				int year = reader.GetInt16(0);
				string name = reader.GetString(1);
				string establishment = reader.GetString(2);
				string category = reader.GetString(3);
				string regime = reader.GetString(4);
				string degree = reader.GetString(5);
				string institution = reader.GetString(6);
				listTeacher.Add(new Person(year, name, establishment, category, regime, degree, institution));
			}
			// clean up
			reader.Close();
			reader = null;
			dbcmd.Dispose();
			dbcmd = null;
			dbcon.Close();
			dbcon = null;
			
			return listTeacher;
		}
		
        /// <summary>
        /// Get The Number of teachers by any resourse on establishmenet
        /// </summary>
        /// <param name="year">Year of the data</param>
        /// <param name="what">Resouse to count</param>
        /// <returns>The Dictionary of Establishments with everiting counted</returns>
		public static Dictionary<string, int> NumberTeachersEstablishmentYear(int year, string what){
			Dictionary<string, int> establishmentDict = GetList("designacao", what);
			
			List<Person> listTeachers = GetListTeachers(year);
			
			if(what.Equals("estabelecimentos")){
				foreach (Person k in listTeachers)
				{
					if (establishmentDict.ContainsKey(k.GetEsblishment))
					{
						establishmentDict[k.GetEsblishment] += 1;
					}
				}
			}else if(what.Equals("graus")){
				foreach (Person k in listTeachers)
				{
					if (establishmentDict.ContainsKey(k.GetDegree))
					{
						establishmentDict[k.GetDegree] += 1;
					}
				}
			}else{
				foreach (Person k in listTeachers)
				{
					if (establishmentDict.ContainsKey(k.GetDegree + " - " + k.GetEsblishment))
					{
						establishmentDict[k.GetDegree + " - " + k.GetEsblishment] += 1;
					}
				}
			}
			
			Console.WriteLine("Removing empaty Values...");
			List<string> toRemove = new List<string>();
			foreach (KeyValuePair<string, int> entry in establishmentDict)
			{
				if(entry.Value == 0){
					toRemove.Add(entry.Key);
				}
			}
			
			foreach (string k in toRemove)
			{
				establishmentDict.Remove(k);
			}
			
			Console.WriteLine("DONE!");
			return establishmentDict;
		}
		
		
		/// <summary>
        /// Get The Doctorate Possibilities on Degrres table
        /// </summary>
        /// <param name="year">Year of the data</param>
        /// <returns>The List of doctorate possibilities</returns>
		public static List<string> GetDoctoratePossebilities(int year){
			Dictionary<string, int> degreeDict = GetList("designacao", "graus");
			
			List<Person> listTeachers = GetListTeachers(year);
			List<string> listPossebilities = new List<string>();
			foreach (KeyValuePair<string, int> entry in degreeDict){
				if(entry.Key.Contains("doktor") || entry.Key.Contains("doutoramento") || entry.Key.Contains("Doctorado") || entry.Key.Contains("Doutoramento")){
					if(!entry.Key.Contains("Pós")){
						listPossebilities.Add(entry.Key);
					}
				}
			}
			return listPossebilities;
		}

        /// <summary>
        /// Get The Number of teachers with docate degree on establishment
        /// </summary>
        /// <param name="year">Year of the data</param>
        /// <returns>The Dictionary of Establishments with everiting counted</returns>
		public static Dictionary<string, int> NumTeachersDoctorYear(int year){
			List<string> listPossebilities = GetDoctoratePossebilities(year);
			
			Dictionary<string, int> doutDict = new Dictionary<string, int>();
			
			List<Person> listTeachers = GetListTeachers(year);
			
			foreach(Person f in listTeachers){
				if(listPossebilities.Contains(f.GetDegree)){
					if(doutDict.ContainsKey(f.GetEsblishment)){
						doutDict[f.GetEsblishment] += 1;
					}else{
						doutDict.Add(f.GetEsblishment, 1);
					}
				}
			}
			Console.WriteLine("200{0}  => {1} teachers");
			return doutDict;
		}
		
		/// <summary>
        /// Get The Lists of teachers in a establishment
        /// </summary>
        /// <param name="year">Year of the data</param>
        /// <returns>The Dictionary of Establishments with Lists of teachers</returns>
		public static Dictionary<string, List<Person>> ListTeachersDoctorYear(int year){
			List<Person> listTeachers = GetListTeachers(year);
			List<string> listPossebilities = GetDoctoratePossebilities(year);
			
			Dictionary<string, List<Person>> listsOnEstablishment = new Dictionary<string, List<Person>>();
			
			foreach (Person k in listTeachers){
				if(listsOnEstablishment.ContainsKey(k.GetEsblishment) && listPossebilities.Contains(k.GetDegree)){
					listsOnEstablishment[k.GetEsblishment].Add(k);
				}else if(!listsOnEstablishment.ContainsKey(k.GetEsblishment) && listPossebilities.Contains(k.GetDegree)) {
					listsOnEstablishment.Add(k.GetEsblishment, new List<Person>());
					listsOnEstablishment[k.GetEsblishment].Add(k);
				}
			}
			
			foreach (KeyValuePair<string, List<Person>> entry in listsOnEstablishment){
				Console.WriteLine("{0}: ", entry.Key);
				foreach (Person k in entry.Value)
				{
					Console.WriteLine(k.GetName);
				}
				Console.WriteLine("\n");
			}
			return listsOnEstablishment;
		}
		
        /// <summary>
        /// Print the list of Establichments
        /// </summary>
        /// <param name="year">Year of the data</param>
        /// <returns>the List of establishments on year selected</returns>
		public static List<string> PrintListEstablishments(int year){
			List<Person> listTeachers = GetListTeachers(year);
			
			List<string> listEstablisments = new List<string>();
			
			foreach (Person k in listTeachers){
				if(!listEstablisments.Contains(k.GetEsblishment)){
					listEstablisments.Add(k.GetEsblishment);
				}
			}
			return listEstablisments;
		}

		/// <summary>
        /// get the lists of persons who left and who enter in the establishment
        /// </summary>
        /// <param name="year">Year of the data</param>
        /// <param name="caseNum">if 10 = leavers List if 11 = newers list</param>
        /// <returns>The Dictionary of Establishments with Lists of leavers/newers Teacchers</returns>
		public static Dictionary<string, List<Person>> PrintLeaversNewersList(int year, int caseNum){
			List<Person> listTeachers = GetListTeachers(year - 1);
			List<Person> listTeachersNext = GetListTeachers(year);
			
			Dictionary<string, List<Person>> listsOnEstablishment = new Dictionary<string, List<Person>>();
			
			if(listTeachers.Count != 0 && listTeachersNext.Count != 0){
				if(caseNum == 10){
					foreach (Person k in listTeachers){
						if(!listTeachersNext.Contains(k) && listsOnEstablishment.ContainsKey(k.GetEsblishment)){
							listsOnEstablishment[k.GetEsblishment].Add(k);
						}else if(!listTeachersNext.Contains(k) && !listsOnEstablishment.ContainsKey(k.GetEsblishment)){
							listsOnEstablishment.Add(k.GetEsblishment, new List<Person>());
							listsOnEstablishment[k.GetEsblishment].Add(k);
						}
					}
				}else if(caseNum == 11){
					foreach (Person k in listTeachersNext){
						if(!listTeachers.Contains(k) && listsOnEstablishment.ContainsKey(k.GetEsblishment)){
							listsOnEstablishment[k.GetEsblishment].Add(k);
						}else if(!listTeachers.Contains(k) && !listsOnEstablishment.ContainsKey(k.GetEsblishment)){
							listsOnEstablishment.Add(k.GetEsblishment, new List<Person>());
							listsOnEstablishment[k.GetEsblishment].Add(k);
						}
					}
				}
				
				Console.WriteLine("Removing Spaces on List...");
				listsOnEstablishment = PrintCleanEnpatySpaces(listsOnEstablishment);
				
			}else{
				Console.WriteLine("ERROR! Can't compare this year with 1999 or above and 2010 or more");
			}
			
			return listsOnEstablishment;
		}
		
        /// <summary>
        /// Get The Number of Migraters
        /// </summary>
        /// <param name="year">Year of the data</param>
        /// <returns>The Number of migraters</returns>
		public static int NumberMigratersTeachers(int year){
			int count = 0;
			List<Person> listTeachers = GetListTeachers(year);
			List<Person> listTeachersNext = GetListTeachers(year + 1);
			
			Dictionary<string, string> listDict = new Dictionary<string, string>();
			
			foreach (Person k in listTeachers){
				if(!listDict.ContainsKey(k.GetName)){
					listDict.Add(k.GetName, k.GetEsblishment);
				}
			}
			
			foreach (Person k in listTeachersNext){
				if(listDict.ContainsKey(k.GetName)){
					if(!listDict[k.GetName].Equals(k.GetEsblishment)){
						count++;
					}
				}
			}
			Console.WriteLine("200{0}   ->    200{1} = {2} teachers", year, year+1, count);
			return count;
			
		}
		
        /// <summary>
        /// Get The List of Migraters teachers
        /// </summary>
        /// <param name="year">Year of the data</param>
        /// <returns>The Dictionary of Establishments with List of Migraters</returns>
		public static Dictionary<string, List<Person>> ListMigratersTeachers(int year){
			List<Person> listTeachers = GetListTeachers(year);
			List<Person> listTeachersNext = GetListTeachers(year + 1);
			
			Dictionary<string, string> listDict = new Dictionary<string, string>();
			Dictionary<string, string> listDictNext = new Dictionary<string, string>();
			
			Dictionary<string, List<Person>> listsOfLeavers = new Dictionary<string, List<Person>>();
			
			foreach (Person k in listTeachersNext){
				if(!listDictNext.ContainsKey(k.GetName)){
					listDictNext.Add(k.GetName, k.GetEsblishment);
				}
			}
			
			foreach (Person k in listTeachers){
				if(listDictNext.ContainsKey(k.GetName)){
					if(!listDictNext[k.GetName].Equals(k.GetEsblishment) && !listsOfLeavers.ContainsKey(k.GetEsblishment)){
						listsOfLeavers.Add(k.GetEsblishment, new List<Person>());
						listsOfLeavers[k.GetEsblishment].Add(k);
					}else if(!listDictNext[k.GetName].Equals(k.GetEsblishment) && listsOfLeavers.ContainsKey(k.GetEsblishment)){
						listsOfLeavers[k.GetEsblishment].Add(k);
					}
				}
			}
			listsOfLeavers = PrintCleanEnpatySpaces(listsOfLeavers);
			return listsOfLeavers;
			
		}

        /// <summary>
        /// Get The Number of teachers have been promoted by establishment
        /// </summary>
        /// <param name="year">Year of the data</param>
        /// <returns>The Dictionary with number of promoted teachers by establishment</returns>
		public static Dictionary<string, int> NumberPromotedTeachers(int year){
			List<Person> listTeachers = GetListTeachers(year);
			List<Person> listTeachersNext = GetListTeachers(year);
			
			Dictionary<string, string> listDict = new Dictionary<string, string>();
			Dictionary<string, int> listPromotedTeachers = new Dictionary<string, int>();
			
			foreach (Person k in listTeachers){
				if(!listDict.ContainsKey(k.GetName)){
					listDict.Add(k.GetName, k.GetCategory);
				}
			}

			foreach (Person k in listTeachersNext){
				if(listDict.ContainsKey(k.GetName)){
					if(!listDict[k.GetName].Equals(k.GetCategory) && !listPromotedTeachers.ContainsKey(k.GetEsblishment)){
						listPromotedTeachers.Add(k.GetEsblishment, 1);
					}else if(!listDict[k.GetName].Equals(k.GetCategory) && listPromotedTeachers.ContainsKey(k.GetEsblishment)){
						listPromotedTeachers[k.GetEsblishment] += 1;
					}if(!listPromotedTeachers.ContainsKey(k.GetEsblishment)){
						listPromotedTeachers.Add(k.GetEsblishment, 0);
					}
				}
			}
			return listPromotedTeachers;
		}
        		
        /// <summary>
        /// Clean every empaty spaces on dictionary
        /// </summary>
        /// <param name="list">dictionaty do clean</param>
        /// <returns>The Same Dictionary cleaned</returns>
		public static Dictionary<string, List<Person>> PrintCleanEnpatySpaces(Dictionary<string, List<Person>> list){
			List<Person> listToRemove = new List<Person>();
			foreach (KeyValuePair<string, List<Person>> entry in list){
				for(int i = 0; i < entry.Value.Count - 1; i++)
				{
					if(entry.Value[i].GetName.Equals(entry.Value[i+1].GetName)){
						listToRemove.Add(entry.Value[i]);
					}
				}
				foreach (Person k in listToRemove)
				{
					entry.Value.Remove(k);
				}
				listToRemove.Clear();

				Console.WriteLine("{0}: ", entry.Key);
				foreach (Person k in entry.Value)
				{
					Console.WriteLine(k.GetName);
				}
				Console.WriteLine("\n");
			}
			return list;
		}
	}
}
